-- SELECT * FROM FN_LOAD_SO_LINE_ITEMS_WITH_BLANK_FGI()
ALTER  FUNCTION FN_LOAD_SO_LINE_ITEMS_WITH_BLANK_FGI()

RETURNS @FN_RESULTS TABLE ( BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT, MFG_PART_NUMBER VARCHAR(50), MFG_PROCESSSTEP_ID INT, MFG_PRODUCT_ID INT, BLANK_FGI_PART_NUMBER VARCHAR(50), BLANK_FGI_PROCESSSTEP_ID INT, BLANK_FGI_PRODUCT_ID INT, BACKLOG_QUANTITY INT,
			    SALES_ORDER_ID INT, SALES_ORDER_NO VARCHAR(50), CUSTOMER_ID INT, LINE_ITEM_ID INT, LINE_ITEM_NO INT, REQUESTED_DATE DATETIME, COOL_REVISED_DATE DATETIME, SCHEDULE_DATE DATETIME, ORDER_TYPE INT, SO_STATUS VARCHAR(20), COOL_STATUS VARCHAR(20), SHIPPED_QUANTITY INT, PARTIAL_SHIPPED_QUANTITY INT )
AS
BEGIN
	DECLARE @RESULTS TABLE ( BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT, MFG_PART_NUMBER VARCHAR(50), MFG_PROCESSSTEP_ID INT, MFG_PRODUCT_ID INT, BLANK_FGI_PART_NUMBER VARCHAR(50), BLANK_FGI_PROCESSSTEP_ID INT, BLANK_FGI_PRODUCT_ID INT, BACKLOG_QUANTITY INT,
			         SALES_ORDER_ID INT, SALES_ORDER_NO VARCHAR(50), CUSTOMER_ID INT, LINE_ITEM_ID INT, LINE_ITEM_NO INT, REQUESTED_DATE DATETIME, COOL_REVISED_DATE DATETIME, SCHEDULE_DATE DATETIME, ORDER_TYPE INT, SO_STATUS VARCHAR(20), COOL_STATUS VARCHAR(20), SHIPPED_QUANTITY INT, PARTIAL_SHIPPED_QUANTITY INT )
		
	INSERT INTO @RESULTS 
	SELECT  ( 
		SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID =  
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
		) AS BASE_PRODUCT,
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END,
		P.PART_NUMBER, P.PROCESSSTEP_ID, P.PRODUCT_ID, 
		'', 0, 0, 
		ISNULL ( COOL.COOL_QTY, 0 )   AS BACKLOG_QTY, SO.SALESORDER_ID, SO.SALESORDER_NO, SO.CUSTOMER_ID, COOL.LINEITEM_ID, COOL.COOL_LINE_ITEM_NO, COOL.COOL_REQUESTED_DATE, COOL.COOL_REVISED_DATE , COOL.COOL_SCHEDULE_DATE, SO.SO_IS_SAMPLE, 
		SO.SO_STATUS, COOL.COOL_STATUS, 
		( SELECT ISNULL( SUM( ISNULL(CSLIT_QTY_SHIPPED , 0 ) ), 0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED') AS SHIPPED_QUANTITY, 
		NULL AS PARTIAL_SHIPPED_QUANTITY
	FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO, PRODUCT P 
	WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND COOL.COOL_STATUS <> 'SHIPPED' AND ( SO.SO_IS_SAMPLE = 1 OR SO.SO_IS_SAMPLE = 2 )
	
	--INSERT @FN_RESULTS SELECT * FROM @RESULTS	

	INSERT INTO @FN_RESULTS 
	SELECT BASE_PRODUCT, BASE_PRODUCT_ID, MFG_PART_NUMBER, MFG_PROCESSSTEP_ID, MFG_PRODUCT_ID, BLANK_FGI_PART_NUMBER, BLANK_FGI_PROCESSSTEP_ID, 
	BLANK_FGI_PRODUCT_ID, BACKLOG_QUANTITY, SALES_ORDER_ID, SALES_ORDER_NO, CUSTOMER_ID, LINE_ITEM_ID, 
	LINE_ITEM_NO, REQUESTED_DATE, COOL_REVISED_DATE, SCHEDULE_DATE, ORDER_TYPE, SO_STATUS, COOL_STATUS,
	SHIPPED_QUANTITY, ( BACKLOG_QUANTITY - SHIPPED_QUANTITY ) AS PARTIAL_SHIPPED_QUANTITY  
	FROM @RESULTS

 	RETURN
END